package com.twj.tools.poi;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @author LJC
 * @version 1.0
 * @project 大数据云平台项目
 * @package com.css.dsjypt.zjckpt.common.util
 * @file ImportExcelUtil.java 创建时间:2019-11-16下午4:02:29
 * @title 标题（要求能简洁地表达出类的功能和职责）
 * @description 描述（简要描述类的职责、实现方式、使用注意事项等）
 * @copyright Copyright (c) 2019 中国软件与技术服务股份有限公司
 * @company 中国软件与技术服务股份有限公司
 * @module 模块: 模块名称
 * @reviewer 审核人
 * @history 修订历史（历次修订内容、修订人、修订时间等）
 */
public class ImportExcelUtil {
    /**
     * @param filePath 文件路径
     * @return List<List < Object>>
     * @throws Exception
     * @name 读取excel
     * @description 相关说明
     * @time 创建时间:2018-7-4上午11:52:52
     * @author LJC
     * @history 修订历史（历次修订内容、修订人、修订时间等）
     */
    public static List<List<Object>> importExcel(String filePath) throws Exception {
        if (filePath == null || filePath.equals("")) {
            throw new Exception("构造Excel导入器失败，未指定文件全名。");
        }
        final File file = new File(filePath);
        if (!file.exists()) {
            throw new Exception("构造Excel导入器失败，指定的文件不存在：" + filePath);
        }
        if (filePath.endsWith("xls")) {
            final InputStream imput = new FileInputStream(new File(filePath));
            final HSSFWorkbook workbook = new HSSFWorkbook(imput);
            return xlsExcel(workbook, 0, 1);
        } else {
            final InputStream imput = new FileInputStream(new File(filePath));
            final XSSFWorkbook workbook = new XSSFWorkbook(imput);
            return xlsxExcel(workbook, 0, 1);
        }
    }

    /**
     * @param filePath    文件路径
     * @param sheetNumber 需要读取的 sheet 页（从0开始）
     * @param rowNumber   需要从第几行开始读（从0开始）
     * @return List<List < Object>>
     * @throws Exception
     * @name 读取excel
     * @description 相关说明
     * @time 创建时间:2018-7-4上午11:52:52
     * @author LJC
     * @history 修订历史（历次修订内容、修订人、修订时间等）
     */
    public static List<List<Object>> importExcel(String filePath, int sheetNumber, int rowNumber) throws Exception {
        if (filePath == null || filePath.equals("")) {
            throw new Exception("构造Excel导入器失败，未指定文件全名。");
        }
        final File file = new File(filePath);
        if (!file.exists()) {
            throw new Exception("构造Excel导入器失败，指定的文件不存在：" + filePath);
        }
        if (filePath.endsWith("xls")) {
            final InputStream imput = new FileInputStream(new File(filePath));
            final HSSFWorkbook workbook = new HSSFWorkbook(imput);
            return xlsExcel(workbook, sheetNumber, rowNumber);
        } else {
            final InputStream imput = new FileInputStream(new File(filePath));
            final XSSFWorkbook workbook = new XSSFWorkbook(imput);
            return xlsxExcel(workbook, sheetNumber, rowNumber);
        }
    }

    /**
     * @param workbook    workbook
     * @param sheetNumber 需要读取的 sheet 页（从0开始）
     * @param rowNumber   需要从第几行开始读（从0开始）
     * @return List<List < Object>>
     * @name 中文名称
     * @description 相关说明
     * @time 创建时间:2017-4-7下午5:23:44
     * @author 作者
     * @history 修订历史（历次修订内容、修订人、修订时间等）
     */
    public static List<List<Object>> xlsExcel(HSSFWorkbook workbook, int sheetNumber, int rowNumber) {
        final List<List<Object>> list = new ArrayList<List<Object>>();
        // 创建Excel，读取文件内容
        //final HSSFWorkbook workbook = new HSSFWorkbook(in);
        // 读取默认第一个工作表sheet
        final HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        final HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
        final DecimalFormat df = new DecimalFormat("0");// 格式化 number String
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        int counter = rowNumber;
        final int lastRowNum = sheet.getLastRowNum();
        Boolean rowNull = true;
        for (int i = counter; counter <= lastRowNum; i++) {
            row = sheet.getRow(i);
            rowNull = true;
            if (row == null) {
                continue;
            } else {
                counter++;
            }
            final List<Object> cellList = new ArrayList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    value = null;
                    continue;
                }
                value = getValue(evaluator, df, cell);
                if (!(value == null)) {
                    rowNull = false;
                }
                cellList.add(value);
            }
            if (rowNull) {
                continue;
            }
            list.add(cellList);
        }
        return list;
    }

    /**
     * @param workbook    workbook
     * @param sheetNumber 需要读取的 sheet 页（从0开始）
     * @param rowNumber   需要从第几行开始读（从0开始）
     * @return List<List < Object>>
     * @name 中文名称
     * @description 相关说明
     * @time 创建时间:2017-4-7下午5:23:44
     * @author 作者
     * @history 修订历史（历次修订内容、修订人、修订时间等）
     */
    public static List<List<Object>> xlsxExcel(XSSFWorkbook workbook, int sheetNumber, int rowNumber) {
        final List<List<Object>> list = new ArrayList<List<Object>>();
        // 读取默认第一个工作表sheet
        final XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        final XSSFFormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        final DecimalFormat df = new DecimalFormat("0");// 格式化 number String
        Object value = null;
        XSSFRow row = null;
        XSSFCell cell = null;
        int counter = rowNumber;
        Boolean rowNull = true;
        List<Object> cellList;
        final int lastRowNum = sheet.getLastRowNum();
        for (int i = counter; counter <= lastRowNum; i++) {
            rowNull = true;
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            } else {
                counter++;
            }

            cellList = new ArrayList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:

                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else {
                            final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = null;
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        final CellValue tempCellValue = evaluator.evaluate(cell);
                        final double iCellValue = tempCellValue.getNumberValue();
                        value = String.format("%.4f", iCellValue);
                        break;
                    default:
                        value = null;
                }
                if (!(value == null)) {
                    rowNull = false;
                }
                cellList.add(value);
            }
            if (rowNull) {
                continue;
            }
            list.add(cellList);
        }
        return list;
    }

    /**
     * @param is 流
     * @return java.util.List<java.util.List < java.lang.Object>>
     * @desc
     * @author twj
     * @date 2022/12/7  17:24
     */
    public static List<List<Object>> importExcel(InputStream is) throws Exception {
        // 创建Excel，读取文件内容
        final HSSFWorkbook workbook = new HSSFWorkbook(is);
        // 读取默认第一个工作表sheet
        final int sheetNumber = 0;
        // 从第1行开始读(索引是0开始)
        final int rowNumber = 2;
        final List<List<Object>> returnList = new ArrayList<List<Object>>();
        final HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
        final HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
        // 格式化 number String
        final DecimalFormat df = new DecimalFormat("0");
        final int lastRowNum = sheet.getLastRowNum();
        for (int i = rowNumber; i <= lastRowNum; i++) {
            final HSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            final List<Object> cellList = new ArrayList<Object>();
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                final HSSFCell cell = row.getCell(j);
                if (Objects.nonNull(cell)) {
                    cellList.add(getValue(evaluator, df, cell));
                }
            }
            returnList.add(cellList);
        }
        return returnList;
    }

    /**
     * 处理单元格值
     *
     * @param evaluator evaluator
     * @param df        df
     * @param cell      cell
     * @return 单元格值
     */
    private static Object getValue(HSSFFormulaEvaluator evaluator, DecimalFormat df, HSSFCell cell) {
        final Object value;
        switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_NUMERIC:

                if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = df.format(cell.getNumericCellValue());
                } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    final Date date = (Date) cell.getDateCellValue();
                    final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    value = sdf.format(date);
                } else {
                    value = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                value = null;
                break;
            case HSSFCell.CELL_TYPE_FORMULA:
                final CellValue tempCellValue = evaluator.evaluate(cell);
                final double iCellValue = tempCellValue.getNumberValue();
                value = String.format("%.4f", iCellValue);
                break;
            default:
                value = null;
        }
        return value;
    }
}
